package org.jeecg;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.locationtech.jts.geom.*;
import org.locationtech.jts.io.WKBWriter;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;

import java.io.File;
import java.sql.*;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.atomic.AtomicLong;

public class ShpImporterMultiPolygonToPolygonSimple {

    // 坐标系转换器（32650 -> 4326）
    private static MathTransform transform;

    static {
        try {
            CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:32650");
            CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:4326");
            transform = CRS.findMathTransform(sourceCRS, targetCRS, true);
        } catch (FactoryException e) {
            throw new RuntimeException("坐标系转换器初始化失败", e);
        }
    }

    // 配置参数
    private static final String JDBC_URL = "jdbc:postgresql://123.56.202.52:5432/gisdb";
    private static final String USERNAME = "postgres";
    private static final String PASSWORD = "wwq20010215";
    private static final String TABLE_NAME = "geotype_wgs84_polygons";

    // 批量大小
    private static final int BATCH_SIZE = 5000;

    public static void main(String[] args) {
        String shpPath = "F:\\地质数据\\地形坡度\\地形坡度底图.shp";

        Connection conn = null;
        PreparedStatement pstmt = null;
        SimpleFeatureIterator iterator = null;
        DataStore shpDataStore = null;
        WKBWriter wkbWriter = new WKBWriter();

        // 性能统计
        AtomicLong processedThisRun = new AtomicLong(0);
        AtomicLong skippedCount = new AtomicLong(0);
        long programStart = System.currentTimeMillis();

        try {
            // 1. 初始化数据库连接
            conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            conn.setAutoCommit(false);

            // 2. 查询数据库现有记录数
            long existingCount = getRecordCount(conn, TABLE_NAME);
            System.out.println("数据库现有记录数: " + existingCount);

            // 3. 获取数据库中已存在的ID集合
            Set<Long> existingIds = getExistingIds(conn, TABLE_NAME, existingCount);
            System.out.println("数据库中已存在的ID数量: " + existingIds.size());

            // 4. 加载Shapefile
            System.out.println("正在打开Shapefile: " + shpPath);
            File shpFile = new File(shpPath);
            shpDataStore = DataStoreFinder.getDataStore(Map.of(
                    "url", shpFile.toURI().toURL(),
                    "memory mapped buffer", Boolean.TRUE
            ));
            if (shpDataStore == null) throw new RuntimeException("无法加载Shapefile数据源");

            String typeName = shpDataStore.getTypeNames()[0];
            SimpleFeatureCollection features = shpDataStore.getFeatureSource(typeName).getFeatures();
            long featureCount = features.size();
            System.out.println("Shapefile总记录数: " + featureCount);

            // 5. 创建目标表（如果不存在）
            createTableIfNotExists(conn, TABLE_NAME);

            // 6. 准备插入语句
            String insertSQL = "INSERT INTO " + TABLE_NAME + " (id, the_geom, gridcode) " +
                    "VALUES (?, ST_GeomFromWKB(?, 4326), ?) " +
                    "ON CONFLICT (id) DO NOTHING"; // 防止重复插入
            pstmt = conn.prepareStatement(insertSQL);

            // 7. 获取Feature迭代器
            iterator = features.features();

            // 8. 导入数据（跳过数据库中已存在的记录）
            int currentBatch = 0;
            long batchStartTime = System.currentTimeMillis();
            long lastProgressTime = System.currentTimeMillis();
            long totalToProcess = featureCount - existingIds.size();

            System.out.println("开始转换并导入数据...");
            System.out.println("需要处理的新记录数: " + totalToProcess);

            while (iterator.hasNext()) {
                SimpleFeature feature = iterator.next();
                Long featureId = (Long) feature.getAttribute("Id");

                // 跳过ID为空的记录
                if (featureId == null) {
                    System.out.println("警告: 跳过ID为空的记录");
                    continue;
                }

                // 跳过数据库中已存在的记录
                if (existingIds.contains(featureId)) {
                    skippedCount.incrementAndGet();
                    continue;
                }

                try {
                    // 几何对象 - 转换为WGS84坐标系
                    MultiPolygon originalGeom = (MultiPolygon) feature.getAttribute("the_geom");
                    Geometry transformedGeom = JTS.transform(originalGeom, transform);

                    // 提取第一个多边形
                    Polygon polygon = extractFirstPolygon(transformedGeom, featureId);

                    // gridcode (Long)
                    Long gridcode = (Long) feature.getAttribute("gridcode");

                    // 设置参数
                    pstmt.setLong(1, featureId);
                    pstmt.setBytes(2, wkbWriter.write(polygon));
                    pstmt.setLong(3, gridcode != null ? gridcode : -1);
                    pstmt.addBatch();

                    currentBatch++;
                    processedThisRun.incrementAndGet();

                    // 定期提交批次
                    if (currentBatch >= BATCH_SIZE) {
                        executeBatch(conn, pstmt, currentBatch, batchStartTime);
                        currentBatch = 0;
                        batchStartTime = System.currentTimeMillis();
                    }

                    // 每5秒打印一次进度
                    long currentTime = System.currentTimeMillis();
                    if (currentTime - lastProgressTime > 5000) {
                        lastProgressTime = currentTime;
                        double progress = processedThisRun.get() * 100.0 / totalToProcess;
                        System.out.printf("进度: %.1f%%, 已处理: %d, 跳过: %d%n",
                                progress, processedThisRun.get(), skippedCount.get());
                    }
                } catch (Exception e) {
                    System.err.println("处理记录[" + featureId + "]失败: " + e.getMessage());
                    e.printStackTrace();
                    // 跳过错误记录继续处理
                }
            }

            // 执行最后一批
            if (currentBatch > 0) {
                executeBatch(conn, pstmt, currentBatch, batchStartTime);
            }

            // 9. 完成导入
            System.out.println("✅ 所有记录处理完成!");

            // 10. 创建空间索引（如果不存在）
            createSpatialIndexIfNotExists(conn, TABLE_NAME);

            // 11. 验证几何数据
            verifyGeometry(conn, TABLE_NAME);

            // 12. 打印最终统计
            long dbCount = getRecordCount(conn, TABLE_NAME);
            System.out.println("========================================");
            System.out.printf("数据库现有记录数: %d 条%n", dbCount);
            System.out.printf("Shapefile总记录数: %d 条%n", featureCount);
            System.out.printf("本次导入记录数: %d 条%n", processedThisRun.get());
            System.out.printf("跳过记录数: %d 条%n", skippedCount.get());
            System.out.println("========================================");

        } catch (Exception e) {
            System.err.println("导入失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            closeResources(iterator, shpDataStore, pstmt, conn);
            long totalProgramTime = System.currentTimeMillis() - programStart;
            System.out.println("========================================");
            System.out.printf("程序执行时间: %d ms (%.1f 分钟)%n",
                    totalProgramTime, totalProgramTime / 60000.0);
            System.out.println("========================================");
        }
    }

    /**
     * 提取MultiPolygon中的第一个Polygon
     */
    private static Polygon extractFirstPolygon(Geometry geometry, Long id) {
        if (geometry instanceof Polygon) {
            return (Polygon) geometry;
        } else if (geometry instanceof MultiPolygon) {
            MultiPolygon multiPolygon = (MultiPolygon) geometry;
            if (multiPolygon.getNumGeometries() > 0) {
                Geometry firstGeom = multiPolygon.getGeometryN(0);
                if (firstGeom instanceof Polygon) {
                    return (Polygon) firstGeom;
                } else {
                    System.err.printf("警告: ID %d 的MultiPolygon的第一个几何部件不是Polygon (实际: %s)%n",
                            id, firstGeom.getGeometryType());
                }
            } else {
                System.err.printf("警告: ID %d 的MultiPolygon没有包含任何几何部件%n", id);
            }
        } else {
            System.err.printf("警告: ID %d 的几何对象既不是Polygon也不是MultiPolygon (实际: %s)%n",
                    id, geometry.getGeometryType());
        }

        throw new RuntimeException("无法从几何对象中提取有效的Polygon");
    }

    // ================ 数据库操作 ================ //

    /**
     * 创建目标表（如果不存在）
     */
    private static void createTableIfNotExists(Connection conn, String tableName)
            throws SQLException {
        String createSQL = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "id BIGINT PRIMARY KEY, " +
                "the_geom GEOMETRY(Polygon, 4326), " +
                "gridcode BIGINT)";

        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createSQL);
            conn.commit();
            System.out.println("目标表准备就绪: " + tableName);
        }
    }

    /**
     * 获取数据库中已存在的ID集合
     */
    private static Set<Long> getExistingIds(Connection conn, String tableName, long recordCount)
            throws SQLException {
        Set<Long> existingIds = new HashSet<>();

        // 如果表不存在或没有数据，返回空集合
        if (recordCount == 0) {
            return existingIds;
        }

        // 查询所有ID
        String sql = "SELECT id FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                existingIds.add(rs.getLong("id"));
            }
        }
        return existingIds;
    }

    /**
     * 执行批处理
     */
    private static void executeBatch(Connection conn, PreparedStatement pstmt, int batchSize, long batchStartTime)
            throws SQLException {
        long start = System.currentTimeMillis();

        try {
            int[] results = pstmt.executeBatch();
            conn.commit();
            long duration = System.currentTimeMillis() - batchStartTime;

            // 计算成功插入的数量
            int successCount = 0;
            for (int result : results) {
                if (result >= 0 || result == Statement.SUCCESS_NO_INFO) {
                    successCount++;
                }
            }

            double rate = successCount * 1000.0 / duration;
            System.out.printf("✅ 提交批次: %d条, 成功: %d条, 耗时: %d ms, 速率: %.0f 条/秒%n",
                    batchSize, successCount, duration, rate);
        } catch (BatchUpdateException e) {
            System.err.println("批处理失败: " + e.getMessage());
            conn.rollback();
        } finally {
            pstmt.clearBatch();
        }
    }

    /**
     * 获取数据库记录数
     */
    private static long getRecordCount(Connection conn, String tableName) throws SQLException {
        // 首先检查表是否存在
        if (!tableExists(conn, tableName)) {
            return 0;
        }

        String sql = "SELECT COUNT(*) FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    /**
     * 检查表是否存在
     */
    private static boolean tableExists(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT EXISTS ("
                + "SELECT 1 "
                + "FROM information_schema.tables "
                + "WHERE table_name = ?"
                + ")";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, tableName.toLowerCase());
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBoolean(1);
                }
            }
        }
        return false;
    }

    // ================ 空间索引和验证 ================ //

    /**
     * 创建空间索引（如果不存在）
     */
    private static void createSpatialIndexIfNotExists(Connection conn, String tableName)
            throws SQLException {
        // 如果表不存在，不需要创建索引
        if (!tableExists(conn, tableName) || getRecordCount(conn, tableName) == 0) {
            return;
        }

        String indexName = tableName + "_geom_idx";

        // 检查索引是否存在
        String checkSQL = "SELECT 1 FROM pg_indexes WHERE indexname = ?";
        boolean indexExists = false;

        try (PreparedStatement pstmt = conn.prepareStatement(checkSQL)) {
            pstmt.setString(1, indexName);
            try (ResultSet rs = pstmt.executeQuery()) {
                indexExists = rs.next();
            }
        }

        if (!indexExists) {
            try (Statement stmt = conn.createStatement()) {
                String indexSQL = "CREATE INDEX " + indexName + " " +
                        "ON " + tableName + " USING GIST (the_geom)";
                stmt.execute(indexSQL);
                conn.commit();
                System.out.println("空间索引创建成功");

                // 收集统计信息
                stmt.execute("ANALYZE " + tableName);
                conn.commit();
                System.out.println("统计信息更新完成");
            }
        } else {
            System.out.println("空间索引已存在，跳过创建");
        }
    }

    /**
     * 验证几何数据
     */
    private static void verifyGeometry(Connection conn, String tableName)
            throws SQLException {
        // 如果表不存在或没有数据，跳过验证
        if (getRecordCount(conn, tableName) == 0) {
            return;
        }

        String sql = "SELECT ST_SRID(the_geom) AS srid, " +
                "ST_GeometryType(the_geom) AS geom_type, " +
                "COUNT(id) AS total_count " +
                "FROM " + tableName + " " +
                "GROUP BY srid, geom_type";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("几何数据验证结果:");
            System.out.println("----------------------------------------");

            while (rs.next()) {
                int srid = rs.getInt("srid");
                String geomType = rs.getString("geom_type");
                long count = rs.getLong("total_count");

                System.out.printf("坐标系: SRID=%d, 几何类型: %s, 记录数: %d%n",
                        srid, geomType, count);

                if (srid == 4326) {
                    System.out.println("✅ 坐标系验证成功");
                } else {
                    System.out.println("❌ 坐标系验证失败 (期望4326)");
                }

                if ("ST_Polygon".equalsIgnoreCase(geomType)) {
                    System.out.println("✅ 几何类型验证成功");
                } else {
                    System.out.println("❌ 几何类型验证失败 (期望ST_Polygon)");
                }

                System.out.println("----------------------------------------");
            }
        }
    }

    // ================ 资源清理 ================ //

    /**
     * 安全关闭所有资源
     */
    private static void closeResources(SimpleFeatureIterator iterator, DataStore shpDataStore,
                                       PreparedStatement pstmt, Connection conn) {
        try {
            if (iterator != null) iterator.close();
        } catch (Exception e) {
            System.err.println("关闭iterator失败: " + e.getMessage());
        }
        try {
            if (shpDataStore != null) shpDataStore.dispose();
        } catch (Exception e) {
            System.err.println("关闭shpDataStore失败: " + e.getMessage());
        }
        try {
            if (pstmt != null) pstmt.close();
        } catch (SQLException e) {
            System.err.println("关闭pstmt失败: " + e.getMessage());
        }
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            System.err.println("关闭conn失败: " + e.getMessage());
        }
    }
}